package com.yikome.order.service;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableMap;
import com.yikome.common.CommonService;
import com.yikome.order.dto.OrderDTO;
import com.yikome.order.dto.OrderSkuDTO;
import com.yikome.order.entity.Order;
import com.yikome.order.entity.OrderAddress;
import com.yikome.order.entity.OrderProduct;
import com.yikome.order.enums.OrderStatus;
import com.yikome.order.enums.Payment;
import com.yikome.order.exportDTO.ExportOrderDTO;
import com.yikome.order.exportDTO.ExportOrderProductDTO;
import com.yikome.order.repository.OrderAddressRepository;
import com.yikome.order.repository.OrderExtRepository;
import com.yikome.order.repository.OrderProductRepository;
import com.yikome.order.repository.OrderRepository;
import com.yikome.order.request.OrderRequest;
import com.yikome.utils.NamedParameterJdbcUtils;

import cn.hutool.core.util.StrUtil;
import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;

@Service
public class OrderService extends CommonService<Order, Long> {

	@Value("${admin.remoteurl}")
	private String adminUrl ;
	
	@Autowired
	private OrderRepository orderRepository ; 
	
	@Autowired
	private OrderProductRepository orderProductRepository;
	
	@Autowired
	private OrderAddressRepository orderAddressRepository; 
	
	@Autowired
	private OrderExtRepository orderExtRepository; 
	
	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate ;
	
	@Autowired
	public void setResourceDao(OrderRepository repository) {
		super.setCommonDao(repository);
	}
	
	public Page<OrderDTO> findOrder(OrderRequest orderRequest , Pageable pageable){
		
		String contentSQL = "SELECT \n" + 
							"	o_order.id order_id, o_order.`code`, o_order.price, o_order.freight, o_order.remark, o_order.`status`, o_order.created_at, o_order.payment, \n" + 
							"	o_order_ext.id order_ext_id, o_order_ext.affiliate, o_order_ext.adlk, o_order_ext.pixel_id, o_order_ext.shop_name, o_order_ext.ip, o_order_ext.promotions,\n" + 
							"	o_order_address.id order_address_id, o_order_address.consignee, o_order_address.tel, o_order_address.city, o_order_address.district, o_order_address.details, o_order_address.email \n" + 
							"FROM o_order \n" + 
							"	LEFT JOIN o_order_ext ON (o_order.`code` = o_order_ext.order_code) \n" + 
							"	LEFT JOIN o_order_address ON (o_order.`code` = o_order_address.order_code) \n"; 
		
		
		Map<String , Object> paramsMap = new HashMap<>();
    	StringBuilder paramsSQL = new StringBuilder("WHERE o_order.yn = 1 \n") ;

    	if(orderRequest.getPayment() != null) {
    		paramsSQL.append("AND o_order.payment = :payment \n");
    		paramsMap.put("payment", orderRequest.getPayment());
    	}
    	if(orderRequest.getStatus() != null) {
    		paramsSQL.append("AND o_order.`status` = :statusCode \n");
    		paramsMap.put("statusCode", orderRequest.getStatus());
    	}
    	if(orderRequest.getBegainTime() != null && orderRequest.getEndTime() != null) {
    		paramsSQL.append("AND o_order.created_at BETWEEN FROM_UNIXTIME(:begainTime) AND FROM_UNIXTIME(:endTime) \n");
    		paramsMap.put("begainTime", orderRequest.getBegainTime());
    		paramsMap.put("endTime", orderRequest.getEndTime());
    	}
    	if(orderRequest.getShopId() != null) {
    		paramsSQL.append("AND o_order_ext.shop_id = :shopId \n");
    		paramsMap.put("shopId", orderRequest.getShopId());
    	}
    	if(StrUtil.isNotBlank(orderRequest.getPixelId())) {
    		paramsSQL.append("AND o_order_ext.pixel_id = :pixelId \n");
    		paramsMap.put("pixelId", orderRequest.getPixelId());
    	}
    	if(StrUtil.isNotBlank(orderRequest.getAffiliate())) {
    		paramsSQL.append("AND o_order_ext.affiliate = :affiliate \n");
    		paramsMap.put("affiliate", orderRequest.getAffiliate());
    	}
    	if(orderRequest.getAdlk() != null) {
    		paramsSQL.append("AND o_order_ext.adlk = :adlk \n");
    		paramsMap.put("adlk", orderRequest.getAdlk());
    	}
        if(StrUtil.isNotBlank(orderRequest.getCode())) {
            List<String> orderCodes = StrUtil.split(orderRequest.getCode().replaceAll("\\s", ","), ',');
            if(orderCodes.size() == 1) {
                paramsSQL.append("AND o_order.`code` = :orderCode \n");
                paramsMap.put("orderCode", orderRequest.getCode());
            }else {
                paramsSQL.append("AND o_order.`code` in (:orderCodes) \n");
                paramsMap.put("orderCodes", orderCodes);
            }
        }
        if(StrUtil.isNotBlank(orderRequest.getTel())) {
            List<String> tels = StrUtil.split(orderRequest.getTel().replaceAll("\\s", ","), ',');
            if(tels.size() == 1) {
                paramsSQL.append("AND o_order_address.tel = :tel \n");
                paramsMap.put("tel", orderRequest.getTel());
            }else {
                paramsSQL.append("AND o_order_address.tel in (:tels) \n");
                paramsMap.put("tels", tels);
            }
        }
        if(StrUtil.isNotBlank(orderRequest.getConsignee())) {
            List<String> consignees = StrUtil.split(orderRequest.getConsignee().replaceAll("\\s", ","), ',');
            if(consignees.size() == 1) {
                paramsSQL.append("AND o_order_address.consignee = :consignee \n");
                paramsMap.put("consignee", orderRequest.getConsignee());
            }else {
                paramsSQL.append("AND o_order_address.consignee in (:consignees) \n");
                paramsMap.put("consignees", consignees);
            }
        }
    	
    	String orderSQL = NamedParameterJdbcUtils.findOrderSQL(pageable);
    	String pageSQL = NamedParameterJdbcUtils.findPageSQL(pageable);
    	
    	List<OrderDTO> orderDTOs = jdbcTemplate.query(contentSQL + paramsSQL + orderSQL + pageSQL , paramsMap, new BeanPropertyRowMapper<>(OrderDTO.class));
    	
    	for (OrderDTO orderDTO : orderDTOs) {
    		orderDTO.setPaymentName(Payment.name(orderDTO.getPayment()).getName());
    		orderDTO.setStatusName(OrderStatus.OrderStatusByStatus(orderDTO.getStatus()).getStatusName());
    		orderDTO.setOrderSkuDTOs(findOrderProduct(orderDTO.getCode()));
		}
    	
    	Long total = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM (" + contentSQL + paramsSQL + ") T", paramsMap, Long.class);

        return new PageImpl<>(orderDTOs, pageable, total);
	}
	
	public List<ExportOrderDTO> findExportWholeOrder(OrderRequest orderRequest) {
		
		String contentSQL = "SELECT \n" + 
							"	o_order.`code`, o_order.price, o_order.freight, o_order.remark, o_order.`status`, o_order.created_at, o_order.payment, \n" + 
							"	o_order_ext.shop_name, o_order_address.consignee, o_order_address.tel, o_order_address.city, o_order_address.district, o_order_address.details \n" + 
							"FROM o_order \n" + 
							"	LEFT JOIN o_order_ext ON (o_order.`code` = o_order_ext.order_code) \n" + 
							"	LEFT JOIN o_order_address ON (o_order.`code` = o_order_address.order_code) \n" ; 

		Map<String, Object> paramsMap = new HashMap<>();
		StringBuilder paramsSQL = new StringBuilder("WHERE o_order.yn = 1 \n");

		if (orderRequest.getPayment() != null) {
			paramsSQL.append("AND o_order.payment = :payment \n");
			paramsMap.put("payment", orderRequest.getPayment());
		}
		if (orderRequest.getStatus() != null) {
			paramsSQL.append("AND o_order.`status` = :statusCode \n");
			paramsMap.put("statusCode", orderRequest.getStatus());
		}
		if (orderRequest.getBegainTime() != null && orderRequest.getEndTime() != null) {
			paramsSQL
					.append("AND o_order.created_at BETWEEN FROM_UNIXTIME(:begainTime) AND FROM_UNIXTIME(:endTime) \n");
			paramsMap.put("begainTime", orderRequest.getBegainTime());
			paramsMap.put("endTime", orderRequest.getEndTime());
		}
		if (orderRequest.getShopId() != null) {
			paramsSQL.append("AND o_order_ext.shop_id = :shopId \n");
			paramsMap.put("shopId", orderRequest.getShopId());
		}
		if (StrUtil.isNotBlank(orderRequest.getPixelId())) {
			paramsSQL.append("AND o_order_ext.pixel_id = :pixelId \n");
			paramsMap.put("pixelId", orderRequest.getPixelId());
		}
		if (StrUtil.isNotBlank(orderRequest.getAffiliate())) {
			paramsSQL.append("AND o_order_ext.affiliate = :affiliate \n");
			paramsMap.put("affiliate", orderRequest.getAffiliate());
		}
		if (orderRequest.getAdlk() != null) {
			paramsSQL.append("AND o_order_ext.adlk = :adlk \n");
			paramsMap.put("adlk", orderRequest.getAdlk());
		}
		if(StrUtil.isNotBlank(orderRequest.getCode())) {
            List<String> orderCodes = StrUtil.split(orderRequest.getCode().replaceAll("\\s", ","), ',');
            if(orderCodes.size() == 1) {
                paramsSQL.append("AND o_order.`code` = :orderCode \n");
                paramsMap.put("orderCode", orderRequest.getCode());
            }else {
                paramsSQL.append("AND o_order.`code` in (:orderCodes) \n");
                paramsMap.put("orderCodes", orderCodes);
            }
        }
        if(StrUtil.isNotBlank(orderRequest.getTel())) {
            List<String> tels = StrUtil.split(orderRequest.getTel().replaceAll("\\s", ","), ',');
            if(tels.size() == 1) {
                paramsSQL.append("AND o_order_address.tel = :tel \n");
                paramsMap.put("tel", orderRequest.getTel());
            }else {
                paramsSQL.append("AND o_order_address.tel in (:tels) \n");
                paramsMap.put("tels", tels);
            }
        }
        if(StrUtil.isNotBlank(orderRequest.getConsignee())) {
            List<String> consignees = StrUtil.split(orderRequest.getConsignee().replaceAll("\\s", ","), ',');
            if(consignees.size() == 1) {
                paramsSQL.append("AND o_order_address.consignee = :consignee \n");
                paramsMap.put("consignee", orderRequest.getConsignee());
            }else {
                paramsSQL.append("AND o_order_address.consignee in (:consignees) \n");
                paramsMap.put("consignees", consignees);
            }
        }

		List<ExportOrderDTO> exportOrderDTOs = jdbcTemplate.query(contentSQL + paramsSQL, paramsMap, new BeanPropertyRowMapper<>(ExportOrderDTO.class));
		
		for (ExportOrderDTO exportOrderDTO : exportOrderDTOs) {
			
			String orderProductSQL ="SELECT \n" + 
									"	o_order_product.product_id, o_order_product.product_title, o_order_product.product_image_url, \n" + 
									"	o_order_product.product_sku_barcode, o_order_product.product_sku_name, o_order_product.product_price, o_order_product.quantity, o_order_product.product_total_price, \n" + 
									"	p_product_ext.source_number, p_product_ext.source_url, p_product_sku.purchase_price, s_supplier.shop_name \n" + 
									"FROM o_order_product \n" + 
									"	LEFT JOIN p_product_ext ON (o_order_product.product_id = p_product_ext.product_id) \n" + 
									"	LEFT JOIN p_product_sku ON (o_order_product.product_sku_id = p_product_sku.id) \n" + 
									"	LEFT JOIN s_supplier ON (p_product_ext.supplier_id = s_supplier.id) \n" + 
									"WHERE o_order_product.order_code = :orderCode \n" ;
			
			List<ExportOrderProductDTO> exportOrderProductDTOs = jdbcTemplate.query(orderProductSQL, ImmutableMap.of("orderCode", exportOrderDTO.getCode()), new BeanPropertyRowMapper<>(ExportOrderProductDTO.class));
			
			exportOrderDTO.setExportOrderProductDTOs(exportOrderProductDTOs);
		}

		return exportOrderDTOs;
	}

	public List<OrderSkuDTO> findOrderProduct(String orderCode) {
		
		String contentSQL = "SELECT \n" + 
							"	o_order_product.id order_product_id, o_order_product.product_id, o_order_product.product_title, o_order_product.product_image_url, \n" + 
							"	o_order_product.product_sku_barcode, o_order_product.product_sku_name, o_order_product.product_price, o_order_product.quantity, o_order_product.product_total_price \n" + 
							"FROM o_order_product \n" + 
							"WHERE o_order_product.order_code = :orderCode \n" ;
		
		return jdbcTemplate.query(contentSQL, ImmutableMap.of("orderCode", orderCode), new BeanPropertyRowMapper<>(OrderSkuDTO.class));
	}

	/**
	 * remark | status | price
	 */
	@Transactional
	public Order editOrder(Order orderRequest) {
		
		Order order = orderRepository.findOrder(orderRequest.getId());
		Preconditions.checkArgument(order != null, "要修改的数据不存在...");
		
		if(StrUtil.isNotBlank(orderRequest.getRemark()))order.setRemark(orderRequest.getRemark());
		if(orderRequest.getStatus() != null) order.setStatus(orderRequest.getStatus());
		if(orderRequest.getPrice() != null) order.setPrice(orderRequest.getPrice());
		
		orderRepository.save(order);
		
		return order;
	}

	/**
	 * consignee | tel | city | district | details
	 */
	@Transactional
	public OrderAddress editOrderAddress(OrderAddress orderAddressRequest) {
		OrderAddress orderAddress = orderAddressRepository.findOrderAddress(orderAddressRequest.getId());
		Preconditions.checkArgument(orderAddress != null, "要修改的数据不存在...");
		
		if(StrUtil.isNotBlank(orderAddressRequest.getConsignee()))orderAddress.setConsignee(orderAddressRequest.getConsignee());
		if(StrUtil.isNotBlank(orderAddressRequest.getTel()))orderAddress.setTel(orderAddressRequest.getTel());
		if(StrUtil.isNotBlank(orderAddressRequest.getCity()))orderAddress.setCity(orderAddressRequest.getCity());
		if(StrUtil.isNotBlank(orderAddressRequest.getDistrict()))orderAddress.setDistrict(orderAddressRequest.getDistrict());
		if(StrUtil.isNotBlank(orderAddressRequest.getDetails()))orderAddress.setDetails(orderAddressRequest.getDetails());
		
		return orderAddressRepository.save(orderAddress);
	}

	/**
	 * productPrice | quantity
	 */
	@Transactional
	public OrderProduct editOrderProduct(OrderProduct orderProductRequest) {
		
		OrderProduct orderProduct = orderProductRepository.findOrderProduct(orderProductRequest.getId());
		Preconditions.checkArgument(orderProduct != null, "要修改的数据不存在...");
		
		BigDecimal sourceProductTotalPrice = orderProduct.getProductTotalPrice();
		
		if(orderProductRequest.getProductPrice() != null)orderProduct.setProductPrice(orderProductRequest.getProductPrice());
		if(orderProductRequest.getQuantity() != null)orderProduct.setQuantity(orderProductRequest.getQuantity());
		
		Order order = orderRepository.findOrder(orderProduct.getOrderCode());
		Preconditions.checkArgument(order != null, "要修改的数据不存在...");
		
		BigDecimal newProductTotalPrice = orderProduct.getProductPrice().multiply(BigDecimal.valueOf(orderProduct.getQuantity()));
		
		order.setPrice(order.getPrice().subtract(sourceProductTotalPrice).add(newProductTotalPrice));
		
		orderRepository.save(order);
		
		orderProduct.setProductTotalPrice(newProductTotalPrice);
		orderProductRepository.save(orderProduct);
		
		return orderProduct;
	}
	
	@Transactional
	public void deleteOrder(String orderCode) {
		orderRepository.deleteByCode(orderCode);
		orderExtRepository.deleteByOrderCode(orderCode);
		orderProductRepository.deleteByOrderCode(orderCode);
		orderAddressRepository.deleteByOrderCode(orderCode);
		
		HttpResponse execute = HttpRequest.delete(adminUrl + "/admin/order/api/"+ orderCode +"/delete").execute();
		Preconditions.checkArgument(execute.isOk(), "后台订单删除不成功...");
	}

	public void changeStatus(String orderCode, Integer orderStatus) {
		
		if(StrUtil.isBlank(orderCode)) return;
		
		Order order = orderRepository.findOrder(orderCode);
		if(order == null) return ;
		
		order.setStatus(orderStatus);
		
		orderRepository.save(order);
	}
	
}
